package co.fitstart.coach.dao;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import com.demo2do.core.persistence.annotation.Dynamic;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;

import co.fitstart.entity.coach.CoachSigninRecord;
import co.fitstart.entity.coach.CoachSigninStatistic;
import co.fitstart.entity.coach.PrincipalReportRelation;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.site.Site;

@Dynamic("hibernate")
public interface CoachDao {
    
    /**
     * 
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NOT NULL AND manager IS NOT NULL AND site IS NOT NULL")
    public List<PrincipalReportRelation> listPrincipalReportRelation();
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("SELECT relation.site FROM PrincipalReportRelation relation WHERE relation.coach IS NOT NULL AND relation.manager IS NOT NULL AND relation.site IS NOT NULL AND relation.coach = :coach")
    public Site decideSite(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @param month
     * @return
     */
    @Query("FROM CoachSigninStatistic WHERE principal = :coach AND month = :month")
    public CoachSigninStatistic loadCoachSigninStatistic(@Variable("coach") Principal coach, @Variable("month") String month);
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("SELECT SUM(hour) FROM CoachSigninStatistic WHERE principal = :coach")
    public BigDecimal getCoachHour(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("FROM CoachSigninStatistic WHERE principal = :coach ORDER BY month DESC")
    public List<CoachSigninStatistic> listCoachSigninStatistic(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @param month
     * @return
     */
    @Query("FROM CoachSigninRecord WHERE principal = :coach AND month = :month AND hour !=1.5 AND ignored = false AND courseDescription != '1V1赠课' ORDER BY courseDay, courseTime")
    public List<CoachSigninRecord> listCoachSigninRecord(@Variable("coach") Principal coach, @Variable("month") String month);

    /**
     * 
     * @param coach
     * @return
     */
    @Query("SELECT new Map(month AS month, SUM(hour) AS total) FROM CoachSigninRecord WHERE principal = :coach AND hour = 1.5 AND ignored = false GROUP BY month")
    public List<Map<String, Object>> listCoachExperienceSigninStatistic(@Variable("coach") Principal coach);

    /**
     * 
     * @param coach
     * @param month
     * @return
     */
    @Query("SELECT SUM(hour) FROM CoachSigninRecord WHERE principal = :coach AND month = :month AND hour = 1.5 AND ignored = false ")
    public BigDecimal getCoachExperienceSigninStatistic(@Variable("coach") Principal coach, @Variable("month") String month);
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("SELECT new Map(month AS month, SUM(hour) AS total) FROM CoachSigninRecord WHERE principal = :coach AND ((hour = 1.5 AND ignored = false) OR courseDescription = '1V1赠课') GROUP BY month")
    public List<Map<String, Object>> listCoachHideStatistic(@Variable("coach") Principal coach);

    /**
     * 
     * @param coach
     * @param month
     * @return
     */
    @Query("SELECT SUM(hour) FROM CoachSigninRecord WHERE principal = :coach AND month = :month AND ((hour = 1.5 AND ignored = false) OR courseDescription = '1V1赠课') ")
    public BigDecimal getCoachHideStatistic(@Variable("coach") Principal coach, @Variable("month") String month);
}
